using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class InstructorPages_ViewRoster : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadCourseListBox();
        }
    }
    private void LoadCourseListBox()
    {
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader reader;

        string query = @"SELECT 
                            Course.course_Name AS 'COURSE', 
                            Course.course_ID AS 'VALUE' 
                        FROM 
                            Course 
                        WHERE instructor_ID = @ID";

        string connectionString = ConfigurationManager.ConnectionStrings["Capstone"].ConnectionString;

        conn = new SqlConnection(connectionString);

        comm = new SqlCommand(query, conn);

        string id = (string)(Session["ID"]);
        int userID = Convert.ToInt32(id);

        try
        {
            conn.Open();
            comm.Parameters.AddWithValue("@ID", userID);

            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                int i = 0;
                string name = reader["COURSE"].ToString();
                string value = reader["VALUE"].ToString();
                courseList.Items.Insert(i, new ListItem(name, value));
            }

            reader.Close();
        }
        catch
        {
            dbErrorLabel.Text = "Error retrieving author list";
        }
        finally
        {
            conn.Close();
        }
    }
    protected void courseSelect_Click(object sender, EventArgs e)
    {
        LoadRoster();
        studentDetails.Visible = true;
    }
    private void LoadRoster()
    {
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader reader;

        string query = @"SELECT        
                            Student.first_Name AS 'FIRST', 
                            Student.last_Name AS 'LAST', 
                            Student.email AS 'EMAIL', 
                            Student.student_ID AS 'STUDENT_ID'
                        FROM            
                            Student 
                        INNER JOIN
                            Roster ON Student.student_ID = Roster.student_ID 
                        INNER JOIN
                            Course ON Roster.course_ID = Course.course_ID 
                        WHERE
                            Course.course_ID = @course";

        string connectionString = ConfigurationManager.ConnectionStrings["Capstone"].ConnectionString;

        //string id = (string)(Session["ID"]);

        //int instructorID = Convert.ToInt32(id);
        int course = Convert.ToInt32(courseList.SelectedValue);

        conn = new SqlConnection(connectionString);

        comm = new SqlCommand(query, conn);

        //comm.Parameters.AddWithValue("@ID", instructorID);
        comm.Parameters.AddWithValue("@course", course);

        try
        {
            conn.Open();
            reader = comm.ExecuteReader();
            if (reader.HasRows == true)
            {
                grid.DataSource = reader;
                grid.DataKeyNames = new string[] { "STUDENT_ID" };
                grid.DataBind();
            }
            else
            {
                dbErrorLabel.Text = "No students enroled in this class";
            }
            reader.Close();
            
        }
        catch
        {
            //dbErrorLabel.Text = "Error retrieving book list";
        }
        finally
        {
            conn.Close();
        }
    }
    protected void grid_OnSelectedIndexChanged(object sender, EventArgs e)
    {
        BindDetails();
    }
    private void BindDetails()
    {
        //Obtain the index of the selected row
        int selectedRowIndex = grid.SelectedIndex;

        //Read the employeeID
        int studentID = Convert.ToInt32(grid.DataKeys[selectedRowIndex].Value);

        //Define data objects
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader reader;

        string query = @"SELECT
                            student_ID AS 'STUDENT_ID',
                            first_Name AS 'FIRST',
                            last_Name AS 'LAST',
                            phone_Num AS 'PHONE',
                            email AS 'EMAIL',
                            username AS 'USER',
                            status AS 'STATUS'
                        FROM
                            Student
                        WHERE
                            student_ID = @ID";

        //Read the connection string from web.config
        string connectionString = ConfigurationManager.ConnectionStrings["Capstone"].ConnectionString;

        //Initialize connection
        conn = new SqlConnection(connectionString);

        //Create command
        comm = new SqlCommand(query, conn);

        //Add employee id parameter
        comm.Parameters.AddWithValue("@ID", studentID);

        //Enclose database code in Try-Catch-Finally block
        try
        {
            //Open the connection
            conn.Open();

            //Execute the command
            reader = comm.ExecuteReader();
            studentDetails.Visible = true;

            //Fill the grid with data
            studentDetails.DataSource = reader;
            studentDetails.DataKeyNames = new string[] { "STUDENT_ID" };
            studentDetails.DataBind();

            //Close the reader
            reader.Close();
        }
        finally
        {
            //Close the connection
            conn.Close();
        }
    }
}
